#!/use/bin/python
# coding=utf-8
# APP首页指标
import datetime
import pymysql
import json

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X* 100 / Y, 2)
    return rate

# 记录 引流次数pv
def insertHomePagePv(date):
    try:
        conn, cur = getDataConnection()
        # 引流次数pv - 应用圈
        appCycleNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='app cycle'")
        # 引流次数pv - 设置中心
        settingsNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='settings'")
        # 引流次数pv - 便捷面板
        twoCenterNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='twoCenter'")
        # 引流次数pv - 总数
        totalNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' ")

        logDetail = f"{date}日新增-引流次数pv,总数:{totalNum},应用圈:{appCycleNum},设置中心数:{settingsNum},便捷面板:{twoCenterNum}"
        print(logDetail)
        # 报表key
        analysisKey = "homePagePv"
        saveJsonDb(analysisKey, 'appCycle', '应用圈', appCycleNum, date)
        saveJsonDb(analysisKey, 'settings', '设置中心', settingsNum, date)
        saveJsonDb(analysisKey, 'twoCenter', '便捷面板', twoCenterNum, date)
        saveJsonDb(analysisKey, 'total', '总数', totalNum, date)

        # 引流次数pv - 早间开机
        zjkjNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='ZJKJ'")
        # 引流次数pv - 睡觉提醒
        sjtxNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='SJTX'")
        # 引流次数pv - 夜间音量
        yjylNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='YJYL'")
        # 引流次数pv - 追剧提醒
        zjtxNum = selectNumBySql(f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='ZJTX' ")

        logDetail = f"{date}日新增-各服务的点击次数,早间开机:{zjkjNum},睡觉提醒:{sjtxNum},夜间音量:{yjylNum},追剧提醒:{zjtxNum}"
        print(logDetail)
        # 报表key
        analysisKey = "introducePagePv"
        saveJsonDb(analysisKey, 'zjkj', '早间开机', zjkjNum, date)
        saveJsonDb(analysisKey, 'sjtx', '睡觉提醒', sjtxNum, date)
        saveJsonDb(analysisKey, 'yjyl', '夜间音量', yjylNum, date)
        saveJsonDb(analysisKey, 'zjtx', '追剧提醒', zjtxNum, date)

        #各服务点击率 -pv
        zjkjRate = getRateByXY(zjkjNum,totalNum)
        sjtxRate = getRateByXY(sjtxNum,totalNum)
        yjylRate = getRateByXY(yjylNum,totalNum)
        zjtxRate = getRateByXY(zjtxNum,totalNum)

        # 报表key 各服务的点击率PV
        analysisKey = "introducePageRatePv"
        saveJsonDb(analysisKey, 'zjkj', '早间开机', zjkjRate, date)
        saveJsonDb(analysisKey, 'sjtx', '睡觉提醒', sjtxRate, date)
        saveJsonDb(analysisKey, 'yjyl', '夜间音量', yjylRate, date)
        saveJsonDb(analysisKey, 'zjtx', '追剧提醒', zjtxRate, date)

        logDetail = f"{date}日新增-各服务的点击率PV,早间开机:{zjkjRate},睡觉提醒:{sjtxRate},夜间音量:{yjylRate},追剧提醒:{zjtxRate}"
        print(logDetail)

    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 引流次数uv
def insertHomePageUv(date):
    try:
        conn, cur = getDataConnection()
        # 引流次数pv - 应用圈
        appCycleNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='app cycle'")
        # 引流次数pv - 设置中心
        settingsNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='settings'")
        # 引流次数pv - 便捷面板
        twoCenterNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' and JSON_EXTRACT(t.msg,'$.from') ='twoCenter'")
        # 引流次数pv - 总数
        totalNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'homePageShow' ")

        logDetail = f"{date}日新增-引流次数uv,总数:{totalNum},应用圈:{appCycleNum},设置中心数:{settingsNum},便捷面板:{twoCenterNum}"
        print(logDetail)
        # 报表key
        analysisKey = "homePageUv"
        saveJsonDb(analysisKey, 'appCycle', '应用圈', appCycleNum, date)
        saveJsonDb(analysisKey, 'settings', '设置中心', settingsNum, date)
        saveJsonDb(analysisKey, 'twoCenter', '便捷面板', twoCenterNum, date)
        saveJsonDb(analysisKey, 'total', '总数', totalNum, date)

        # 引流次数pv - 早间开机
        zjkjNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='ZJKJ'")
        # 引流次数pv - 睡觉提醒
        sjtxNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='SJTX'")
        # 引流次数pv - 夜间音量
        yjylNum = selectNumBySql(f"select count(DISTINCT(t.active_id))from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='YJYL'")
        # 引流次数pv - 追剧提醒
        zjtxNum = selectNumBySql(f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'introducePageShow' and JSON_EXTRACT(t.msg,'$.scene') ='ZJTX' ")

        logDetail = f"{date}日新增-各服务的点击次数,早间开机:{zjkjNum},睡觉提醒:{sjtxNum},夜间音量:{yjylNum},追剧提醒:{zjtxNum}"
        print(logDetail)
        # 报表key
        analysisKey = "introducePageUv"
        saveJsonDb(analysisKey, 'zjkj', '早间开机', zjkjNum, date)
        saveJsonDb(analysisKey, 'sjtx', '睡觉提醒', sjtxNum, date)
        saveJsonDb(analysisKey, 'yjyl', '夜间音量', yjylNum, date)
        saveJsonDb(analysisKey, 'zjtx', '追剧提醒', zjtxNum, date)

        #各服务点击率 -uv
        zjkjRate = getRateByXY(zjkjNum,totalNum)
        sjtxRate = getRateByXY(sjtxNum,totalNum)
        yjylRate = getRateByXY(yjylNum,totalNum)
        zjtxRate = getRateByXY(zjtxNum,totalNum)

        # 报表key - 各服务的点击率UV
        analysisKey = "introducePageRateUv"
        saveJsonDb(analysisKey, 'zjkj', '早间开机', zjkjRate, date)
        saveJsonDb(analysisKey, 'sjtx', '睡觉提醒', sjtxRate, date)
        saveJsonDb(analysisKey, 'yjyl', '夜间音量', yjylRate, date)
        saveJsonDb(analysisKey, 'zjtx', '追剧提醒', zjtxRate, date)

        logDetail = f"{date}日新增-各服务的点击率UV,早间开机:{zjkjRate},睡觉提醒:{sjtxRate},夜间音量:{yjylRate},追剧提醒:{zjtxRate}"
        print(logDetail)

    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def saveJsonDb(analysisKey,fieldKey,fieldName,fieldValue,refDate):
    try:
        conn, cur = getDataConnection()
        countNum = selectNumBySql(
            f"select count(1) from analysis_dynamic t where 1=1 and  t.ref_date = '{refDate}' and t.analysis_key = '{analysisKey}' and  JSON_EXTRACT(t.msg,'$.field_key') ='{fieldKey}'")
        msgDict = {}
        msgDict["field_key"] = fieldKey
        msgDict["field_name"] = fieldName
        msgDict["field_value"] = fieldValue
        msgJson = json.dumps(msgDict, ensure_ascii=False, sort_keys=False, separators=(',', ':'))

        if countNum == 0:
            # 插入
            insertSql = f"""insert into analysis_dynamic(analysis_key,msg,ref_date) 
                            value('{analysisKey}','{msgJson}','{refDate}')"""
            cur.execute(insertSql)
        else:
            updateSql = f"""update analysis_dynamic set msg = '{msgJson}',update_time = now()  where 1=1 and  ref_date = '{refDate}' and analysis_key = '{analysisKey}' and  JSON_EXTRACT(msg,'$.field_key') ='{fieldKey}'"""
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date



if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    print (f"{date} 日期,更新APP首页报表")
    insertHomePagePv(date)
    insertHomePageUv(date)

